<?php defined('BASEPATH') OR exit('No direct script access allowed');

class Conversion extends MX_Controller
{

    public function __construct()
    {
        parent::__construct();
        $this->load->library('mahana_hierarchy');
    }

    public function index()
    {
        echo "<h2>Hi! This is Data Conversion Utility Programe</h2>";
    }

    function _us_phone_format($str)
    {
        if ($str && $str != ''){
            $str = trim($str);
        $p1 = substr($str, 0, 3);
        $p2 = substr($str, 3, 3);
        $p3 = substr($str, 6, strlen($str));

        $ph = "($p1) $p2-$p3";
        return $ph;
        }else{
            return '';
        }
    }

function convert_post()
{
    $limit = 50000;

    $dump = "select * from TABLE22 where status = 0 group by (CompanyNameLine1) limit 0, $limit";
    $result = $this->db->query($dump)->result();


    foreach ($result as $dump) {
        $count = $this->_check_count($dump->SicId);

        $data = array();
        $location = array();
        $keyword = array();
        $id = $dump->id;
        if ($count <= 20) {

            $cityID = $this->_get_city_id($dump->State, $dump->City);
            $li_cat = $this->category_tree($dump->SicId);
            $li_loc = $this->location_tree($cityID);

            $meta_title = ucwords(strtolower($dump->CompanyNameLine1)) . ", " . $dump->SicDescription . " in " . $dump->City;
            $meta_description = ucwords(strtolower($dump->CompanyNameLine1)) . ", " . $dump->SicDescription . " in " . $dump->City;
            $meta_keyword = ucwords(strtolower($dump->CompanyNameLine1)) . ", " . $dump->SicDescription . ", " . $dump->City;

            $data = array(
                'id' => $dump->id,
                'title' => ucwords(strtolower($dump->CompanyNameLine1)),
                'zip_code' => $dump->ZipCode,
                'address1' => $dump->AddressLine1,
                'address2' => $dump->AddressLine2,
                'description' => $dump->Description,
                'email' => strtolower($dump->EmailAddress),
                'phone1' => $this->_us_phone_format($dump->PhoneNumber),
                'fax' => $this->_us_phone_format($dump->FaxNumber),
                'lat' => $dump->Lat,
                'long' => $dump->Long,
                'website' => strtolower($dump->CompanyUrl),
                'slug' => url_title($dump->CompanyNameLine1, '-', FALSE),
                'meta_title' => $meta_title,
                'meta_keyword' => $meta_keyword,
                'meta_description' => $meta_description,
            );

            //print_r($dump->id); die();
            //$lastID = $dump->id();
           //print $id;

           // print_r($li_loc); die();
            //-- TERM TABLE DATA --//
            if ($li_loc) {

                $ctr = count($li_loc);
                for ($i = 0; $i < $ctr; $i++) {

                    if ($i == 0) {
                        $trm = 'region';
                        $cap = 'Region';
                    }
                    if ($i == 1) {
                        $trm = 'city';
                        $cap = 'City';
                    }

                    if ($i >= 2) {
                        exit();
                    }
                    //$liID = intval($li_loc[$i]);
                    //print $liID;die();
                    $location = array(
                        'post_id' => $id,
                        'term' => $trm,
                        'value' => intval($li_loc[$i]),
                        'caption' => $cap,
                    );
                    $this->db->insert('term1', $location);

                }
            }


            if ($li_cat) {

                $ctr = count($li_cat);
                //print $ctr; die();
                for ($i = 0; $i < $ctr; $i++) {

                    if ($i == 0) {
                        $trm = 'category';
                        $cap = 'Category';
                    }
                    if ($i == 1) {
                        $trm = 'category';
                        $cap = 'Category';
                    }

                    if ($i >= 2) {
                        $trm = 'keyword';
                        $cap = 'Keyword';
                    }

                    $keyword = array(
                        'post_id' => $id,
                        'term' => $trm,
                        'value' => intval($li_cat[$i]),
                        'caption' => $cap,
                    );
                    $this->db->insert('term1', $keyword);

                }
            }


        }

        if ($li_loc && $li_cat && $data) {
            $this->db->trans_start();
            $this->db->insert('post1', $data);
            //$this->db->insert('term1', $location);
            //$this->db->insert('term1', $keyword);

            $this->db->trans_complete();
            if ($this->db->trans_status() === FALSE) {
                $this->db->trans_rollback();
            } else {
                $this->db->trans_commit();
            }

        }


        $sql = "update TABLE22 set status = 51 where id = $dump->id;";
        $this->db->query($sql);


    }


    die('------OK---------');
}

function _check_count($siccode = 0)
{
    $sql = "select count(*) as no from term1 where term = 'keyword' and value = '$siccode'";
    $result = $this->db->query($sql);
    //print $sql;
    if ($result) {
        $result = $result->result();
        //print_r($result[0]->no);
        if ($result[0]->no == 0) {

            return 0;
        } else {

            return $result[0]->no;
        }
    } else {
        return 0;
    }
}


function location_tree($citycode)
{
    $sql = "select id,lineage from location where id = $citycode limit 1";
    $result = $this->db->query($sql);


    if ($result) {
        $result = $this->db->query($sql)->row();
        //$config = array('table' => 'location');
        //$this->mahana_hierarchy->initialize($config);
        $li = $result->lineage;
        $ex = explode('-',$li);
        //$lineage = $this->mahana_hierarchy->get_ancestors($citycode);

        //print_r ($ex); die();
        return $ex;
        //print_r($lineage);

    } else {
        return false;
    }
}

function category_tree($siccode)
{
    $sql = "select id,lineage from category where id = $siccode limit 1";
    $result = $this->db->query($sql);

    if ($result) {
        $result = $this->db->query($sql)->row();
       // $config = array('table' => 'category');
       // $this->mahana_hierarchy->initialize($config);
        //$lineage = $this->mahana_hierarchy->get_ancestors($siccode);
        $li = $result->lineage;
        $ex = explode('-',$li);

        return $ex;
        //print_r($lineage);

    } else {
        return false;
    }
}


function update_category_slug()
{
    $sql = "select id,name from category where slug = ''";
    $result = $this->db->query($sql)->result();
    foreach ($result as $post) {

        $name = $post->name;

        $data = array(
            'slug' => url_title($post->name, '-', FALSE),
            'meta_title' => "List of " . $name . " business and services",
            'meta_keyword' => $name . " business and services",
            'meta_description' => "List of " . $name . " business and services, Trusted local business listings, maps and ratings and reviews.",
            'description' => "Find " . $name . " business and services in your local place. Trusted local business listings, maps with their contact information, address and reviews.",
        );

        $this->db->where('id', $post->id);
        $this->db->update('category', $data);

        //die("COMPLETE");
    }
    die("COMPLETE");
}

function _get_city_id($statePrefix = '', $city = '')
{
    $sql = "select id from location where prefix = '$statePrefix' and name='$city' limit 1";
    //$sql = "SELECT *  FROM `location` WHERE `deep` = 2 AND `name` LIKE 'Russellville' limit 1;";
    //print $sql; die();
    $row = $this->db->query($sql)->row();
    if ($row) {
        return $row->id;
    } else {
        return 0;
    }
}


function create_term_state()
{
    $sql = "select id,state from post where status = 0 limit 0,5000";
    $result = $this->db->query($sql)->result();

    foreach ($result as $post) {
        $data = array(
            'post_id' => $post->id,
            'term' => 'state',
            'caption' => 'State',
            'value' => $post->state
        );
        $this->db->insert('term', $data);
        $this->db->query("update post set status=51 where id = $post->id");


    }
    die("COMPLETE....");
}


function updateCity()
{
    $sql = "select * from term where term='city' and value=0";
    $result = $this->db->query($sql)->result();
    //var_dump($result);

    foreach ($result as $term) {
        //print $term->post_id."<br>";

        $sql2 = "select id,city,state from dump where id = $term->post_id limit 1";
        $result2 = $this->db->query($sql2)->row();

        //print_r($result2);

        $cid = $this->get_city_id($result2->state, $result2->city);
        //print $cid;
        $sql3 = "update term set value = $cid where term='city' and value=0 and post_id=$term->post_id";
        $this->db->query($sql3);

    }

}


function create_post($limit = 5000)
{
    echo "<p> CREATING POST DATA </p>";

    $sql = "select * from dump where status = 0 limit 0,$limit";
    $result = $this->db->query($sql)->result();

    foreach ($result as $result) {
        $data = array(
            'id' => $result->id,
            'title' => 'Dr.',
            'name' => $result->full_name,
            'speciality' => $result->speciality,
            'degree' => $result->title,
            'gender' => $result->gender,
            'country' => 1,
            'region' => $this->get_state_id($result->state),
            'city' => $this->get_city_id($result->state, $result->city),
            'address1' => $result->address1,
            'address2' => $result->address2,
            'zip' => $result->zip,
            'phone' => $result->phone,
            'fax' => $result->fax,
            'email' => strtolower($result->email),
            'website' => strtolower($result->website),
            'since' => $result->since,
            'education' => $result->education,
            'residency' => $result->residency,
            'activity' => $result->activity,
            'hospital' => $result->hospital,
            'clinic' => $result->clinic,
            'unit' => $result->unit,
            'slug' => url_title(strtolower($result->full_name . "-" . $result->id)),
            'status' => 51,
            'hits' => $this->generateRandomNo(3)
        );


        $this->db->trans_start();

        $this->db->insert('post', $data);


        //-- INSERT TERM (COUNTRY) --//
        $data = array(
            'post_id' => $result->id,
            'term' => 'country',
            'caption' => 'Country',
            'value' => 1
        );
        $this->db->insert('term', $data);

        //-- INSERT TERM (State) --//
        $data = array(
            'post_id' => $result->id,
            'term' => 'state',
            'caption' => 'State',
            'value' => $this->get_state_id($result->state)
        );
        $this->db->insert('term', $data);

        //-- INSERT TERM (City) --//
        $data = array(
            'post_id' => $result->id,
            'term' => 'city',
            'caption' => 'City',
            'value' => $this->get_city_id($result->state, $result->city)
        );
        $this->db->insert('term', $data);


        //-- INSERT TERM (Category) --//
        $data = array(
            'post_id' => $result->id,
            'term' => 'category',
            'caption' => 'Speciality',
            'value' => $this->getSpeciality($result->speciality)
        );
        $this->db->insert('term', $data);


        if ($result->hospital != '') {
            //-- INSERT TERM (Hospital) --//
            $data = array(
                'post_id' => $result->id,
                'term' => 'hospital',
                'caption' => 'Hospital',
                'value' => $result->hospital
            );
            $this->db->insert('term', $data);

        }


        if ($result->clinic != '') {
            //-- INSERT TERM (Clinic) --//
            $data = array(
                'post_id' => $result->id,
                'term' => 'clinic',
                'caption' => 'Clinic',
                'value' => $result->clinic
            );
            $this->db->insert('term', $data);
        }

        $sql = "update dump set status=1 where id = $result->id";
        $this->db->query($sql);

        $this->db->trans_complete();


        if ($this->db->trans_status() === FALSE) { // Check if transaction result successful
            echo "<BR>------- TRANS FAILED -------</BR>";
        }


    }


    die("<p>COMPLETE.....</p>");
}

function create_city()
{
    echo "<p> CREATE CITY </p>";
    $config = array('table' => 'location_new');
    $this->load->library('mahana_hierarchy');
    $this->mahana_hierarchy->initialize($config);

    $sql = "select id,stateid,statecode,cityname from city";


    $result = $this->db->query($sql)->result();

    foreach ($result as $result) {

        $data = array(
            'parent_id' => $result->stateid,
            'name' => ucwords($result->cityname),
            'slug' => url_title($result->cityname, '-', FALSE),
            'prefix' => strtoupper($result->statecode),
            'meta_title' => "Find Business &amp; Services in $result->cityname",
            'meta_keyword' => "Business &amp; Services in $result->cityname",
            'meta_description' => "List of Business &amp; Services in $result->cityname, You can find all the Business &amp; Services in your local area with their Contact information, reviews and ratting, which will guide you to choose best Business &amp; Services in $result->cityname"
        );

        $this->mahana_hierarchy->insert($data);
        //die("<p>COMPLETE.....</p>");

        /*
         //$state = $city->state;
        //print $state;
        $sID = $this->get_state_id($result->state);
        if($sID){

        $data = array(
                'parent_id' => $sID,
                'name' => $result->city,
                'slug'	=>  url_title(strtolower($result->city)),
                'prefix' => strtoupper($result->state),
                'cid' => 1,
                'meta_title' => 'Find Doctors &amp; Specialist in '. $result->city,
                'meta_keyword' => "Find Doctors in $result->city, Helth Specialist in $result->city",
                'meta_description' => "Here is the List of Doctors &amp; Helth Specialists in $result->city, You can find all the Doctors &amp; Specilists in your local area with their Contact information, reviews and ratting, which will guide you to choose best Doctor and Specialist in $result->city"
        );

        $this->mahana_hierarchy->insert($data);
        //die("<p>COMPLETE.....</p>");
        }
        */
    }

    die("<p>COMPLETE.....</p>");

}

function create_state()
{
    echo "<p> CREATE STATE </p>";
    $config = array('table' => 'location_new');
    $this->load->library('mahana_hierarchy');
    $this->mahana_hierarchy->initialize($config);

    $sql = "select * from state group by (statename)";
    $result = $this->db->query($sql)->result();

    foreach ($result as $state) {
        $data = array(
            'id' => $state->id,
            'parent_id' => 0,
            'name' => $state->statename,
            'slug' => url_title($state->statename, '-', FALSE),
            'prefix' => $state->statecode,
            'meta_title' => "Find Business &amp; Services in $state->statename",
            'meta_keyword' => "Business &amp; Services in $state->statename",
            'meta_description' => "List of Business &amp; Services in $state->statename, You can find all the Business &amp; Services in your local area with their Contact information, reviews and ratting, which will guide you to choose best Business &amp; Services in $state->statename"
        );

        $this->mahana_hierarchy->insert($data);


    }

    die("COMPLETE..q..."); //print_r($data);

}

function create_country()
{
    $config = array('table' => 'location');
    $this->load->library('mahana_hierarchy');
    $this->mahana_hierarchy->initialize($config);
    $data = array(
        'name' => "Canada",
        'slug' => url_title(strtolower('Canada')),
        'prefix' => "CA"
    );
    //$this->mahana_hierarchy->insert($data);
}


function get_state_id($state)
{
    $sql = "select id from location where prefix = '$state' and deep = 1 limit 1";
    //print $sql; die();
    $row = $this->db->query($sql)->row();
    if ($row) {
        return $row->id;
    } else {
        return 0;
    }
}

function get_city_id($state, $city)
{
    $sql = "select id from location where prefix = '$state' and name='$city' and deep = 2 limit 1";
    $sql = "SELECT *  FROM `location` WHERE `deep` = 2 AND `name` LIKE 'Russellville' limit 1;";
    //print $sql; die();
    $row = $this->db->query($sql)->row();
    if ($row) {
        return $row->id;
    } else {
        return 0;
    }
}

function generateRandomNo($length = 3)
{
    $characters = '0123456789';
    $randomString = '';
    for ($i = 0; $i < $length; $i++) {
        $randomString .= $characters[rand(0, strlen($characters) - 1)];
    }
    return $randomString;
}

function getSpeciality($str = '')
{
    if ($str != '') {
        $str = trim($str);
        $sql = "select id from category where name = '$str' limit 1";
        //print $sql; die();
        $row = $this->db->query($sql)->row();
        if ($row) {
            return $row->id;
        } else {
            return 0;
        }


    } else {
        return 0;
    }

}

}

/* End of file Conversion.php */
/* Location: ./application/controllers/Conversion.php */
